package com.bluegalaxy.nhb.snservice.core.util;

/**
 * Created by LiuShuang on 2016/4/18.
 */

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class Excel {
    /*
        public static void main(String[] args) throws Exception {
              File file = new File("D:/test.xlsx");
              String[][] result = getData07DO(file, 0);
                int rowLength = result.length;
                for(int i=0;i<rowLength;i++) {
                System.out.println(i);
            }

        }*/
    public static String[][] getExcelDate(File file, int index){
        String[][] result=null;
        BufferedInputStream in=null;
        try {
            String type= getExcelType(file);
            in = new BufferedInputStream(new FileInputStream(file),512);
            Workbook wb=null ;
            if("07".equals(type)){
                wb=new XSSFWorkbook(in);
            }
            if("03".equals(type)){
                wb= new HSSFWorkbook(in);
            }
            result=getData(wb,index);
            wb=null;
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                if (in != null) in.close();
            }catch (IOException e){
                e.printStackTrace();
            }
        }
        return  result;
    }

    public static String getExcelType(File file){
        String filename = file.getName();
        String extName= filename.substring(filename.lastIndexOf(".")+1);
        System.out.println();
        if("xlsx".equals(extName)){
            return "07";
        }else if("xls".equals(extName)){
            return "03";
        }else{
            return "";
        }
    }

    /**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     * @param wb 读取数据的源Excel
     * @param ignoreRows 读取数据忽略的行数，可以根据具体情况填写，通常忽略标题行（第一行1）
     */
    public static String[][] getData(Workbook wb, int ignoreRows)
            throws IOException {
        List<String[]> result = new ArrayList<String[]>();

        Cell cell;
        Sheet st = wb.getSheetAt(0);
        int nullRow=0;
        int rowSize =0;
        for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
            Row row = st.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            //如果超过5个空行，则默认这是最后一行。
            if(nullRow==5){
                break;
            }else if (isRowNull(row)) {
                nullRow++;
                continue;
            }
            int tempRowSize = row.getLastCellNum() + 1;
            if (tempRowSize > rowSize) {
                rowSize = tempRowSize;
            }
            String[] values = new String[rowSize];
            Arrays.fill(values, "");
            boolean hasValue = false;

            for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                String value = "";
                cell = row.getCell(columnIndex);
                 if (cell != null) {
                    value=getCellValue(cell);
                }
                values[columnIndex] = rightTrim(value);
                hasValue = true;
            }

            if (hasValue) {
                result.add(values);
            }
        }
        String[][] returnArray = new String[result.size()][rowSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = result.get(i);
        }
        return returnArray;
    }

    public static String getCellValue(Cell cell){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        String value="";
        if(cell==null){
            return value;
        }
        DecimalFormat df = new DecimalFormat("0.###");
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                if(HSSFDateUtil.isCellDateFormatted(cell)){
                    Date date = cell.getDateCellValue();
                    if(date != null || !"".equals(date)){
                        value = sdf.format(date);
                    }
                }else{
                    value= df.format(cell.getNumericCellValue());
                }
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                // 导入时如果为公式生成的数据则无值
                try {
                    FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue cv = evaluator.evaluate(cell);
                    value = cv.getStringValue();
                } catch (IllegalStateException e) {
                    value= String.valueOf(cell.getRichStringCellValue());
                }
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                value = (cell.getBooleanCellValue() ? "Y" : "N");
                break;
        }
        return value.trim();
    }

    /**
     * 去掉字符串右边的空格
     * @param str 要处理的字符串
     * @return 处理后的字符串
     */
    public static String rightTrim(String str) {
        if (str == null) {
            return "";
        }
        int length = str.length();
        for (int i = length - 1; i >= 0; i--) {
            if (str.charAt(i) != 0x20) {
                break;
            }
            length--;
        }
        return str.substring(0, length);
    }
    public static boolean isRowNull(Row row){
        for(int i=0;i<row.getLastCellNum();i++){
            if(!isCellNull(row.getCell(i))){
                return false;
            }
        }
        return true;
    }
    public static boolean isCellNull(Cell cell){
        return cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK || "".equals(getCellValue(cell));
    }

    /**
     * 导出excel
     *
     * @param workbook    工作簿
     * @param title       sheet名称
     * @param index       sheet索引
     * @param result      导出的结果集，必须有对应的vo对象
     * @param clazz       vo对象的类，可以通过反射自动生成cell中的数据
     * @param heads       表头的说明
     * @param headswidth  表头即单元格的宽度
     * @param key         vo中实际需要导出的属性，与表头的数量及内容对应
     * @param validateMap 数据有效性设置，比如某cell为枚举型，需要map<vo属性名，枚举值数组>
     * @param autoRowNum  是否增加一列自动排列的序号
     * @return
     */
    public static XSSFWorkbook exportExcel(XSSFWorkbook workbook, String title, int index,
                                           List<?> result, Class<?> clazz, String[] heads, int[] headswidth, String[] key,
                                           Map<String, String[]> validateMap, boolean autoRowNum) {
        try {
            XSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(index, title);

            XSSFRow row;

            XSSFCellStyle cellStyle = setHeaderStyle(workbook);

            //列索引默认为0
            int colIndex = autoRowNum ? 1 : 0;


            //创建表头cell
            XSSFRow rows = sheet.createRow(0);
            rows.setHeightInPoints(20);
            for (int j = 0; j < heads.length + colIndex; j++) {
                XSSFCell cell = rows.createCell((short) j);
                cell.setCellStyle(cellStyle);
            }

            //录入表头内容
            row = sheet.getRow(0);
            XSSFCell cell;

            if (autoRowNum) {
                //表头中增加“序号”
                cell = row.getCell((short) 0);
                sheet.setColumnWidth((short) 0, (short) (3000));
                cell.setCellValue(new XSSFRichTextString("序号"));

                //生成自动序号
                for (int i = 1; i <= result.size(); i++) {
                    cellStyle = setDetailStyle(workbook, String.class, "");
                    rows = sheet.createRow(i);

                    cell = rows.createCell((short) 0);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(i);
                }
            }

            for (int i = 0; i < heads.length; i++) {
                cell = row.getCell((short) (i + colIndex));
                sheet.setColumnWidth((short) (i + colIndex), (short) (30 * headswidth[i]));
                cell.setCellValue(new XSSFRichTextString(heads[i]));
            }

            for (int j = 0; j < key.length; j++) {
                String fieldName = "";
                String fieldOption = "";
                if (key[j].contains("|")) {
                    String[] tmp = key[j].split("\\|");
                    fieldName = tmp[0];
                    fieldOption = tmp[1];
                } else {
                    fieldName = key[j];
                }
//                Field field = clazz.getDeclaredField(fieldName);
                Field field = getDeclaredField(clazz,fieldName);

                cellStyle = setDetailStyle(workbook, field.getType(), fieldOption);

                //FIXME 这里对结果集的循环，应该考虑表头不止一行的情况
                for (int i = 1; i <= result.size(); i++) {
                    if (!autoRowNum && j == 0) {
                        rows = sheet.createRow(i);
                    } else {
                        rows = sheet.getRow(i);
                    }

                    cell = rows.createCell((short) (j + colIndex));
                    cell.setCellStyle(cellStyle);
                }

                if (validateMap != null && validateMap.get(key[j]) != null) {
                    String[] textList = validateMap.get(key[j]);
                    sheet.addValidationData(setDataValidation(sheet, textList, 0, result.size() + 1, j + colIndex, j + colIndex));
                }
            }

            for (int i = 0; i < result.size(); i++) {
                for (int j = 0; j < key.length; j++) {
                    String fieldName = "";

                    if (key[j].contains("|")) {
                        String[] tmp = key[j].split("\\|");
                        fieldName = tmp[0];

                    } else {
                        fieldName = key[j];
                    }

//                    Field field = clazz.getDeclaredField(fieldName);
                    Field field = getDeclaredField(clazz,fieldName);

                    Method method = clazz.getMethod(toGetter(fieldName));

                    Object object = method.invoke(result.get(i));

                    row = sheet.getRow(1 + i);
                    cell = row.getCell((short) (j + colIndex));

                    if (object == null) {
                        cell.setCellValue(new XSSFRichTextString(""));
                    } else {
                        if (field.getType() == String.class) {
                            cell.setCellValue(new XSSFRichTextString((String) object));
                        } else if (field.getType() == Long.class) {
                            cell.setCellValue(String.valueOf((Long) object));
                        } else if (field.getType() == int.class) {
                            cell.setCellValue((Integer) object);
                        } else if (field.getType() == BigDecimal.class) {
                            cell.setCellValue(((BigDecimal) object).doubleValue());
                        } else if (field.getType() == Date.class) {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            cell.setCellValue(new XSSFRichTextString(sdf.format((Date) object)));
                        } else if (field.getType() == Timestamp.class) {
                            cell.setCellValue((Date) object);
                        } else if (field.getType() == double.class || field.getType() == Double.class) {
                            cell.setCellValue(((Double) object).doubleValue());
                        } else if (field.getType() == Integer.class) {
                            cell.setCellValue(((Integer) object).intValue());
                        }
                    }
                }
            }

//			row = sheet.getRow(3 + result.size());
//			sheet.addMergedRegion(new CellRangeAddress(3+result.size(), (short)0, 3+result.size(), (short)(heads.length-1)));
//			cell = row.getCell((short) 0);
//			cell.setCellValue(new XSSFRichTextString("合计"+result.size()+"条"));

            return workbook;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    private static XSSFCellStyle setTitleStyle(XSSFWorkbook workbook) {
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 20); // 字体大小
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 加粗
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font); // 单元格字体
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平对齐方式
        return cellStyle;
    }

    private static XSSFCellStyle setHeaderStyle(XSSFWorkbook workbook) {
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12); // 字体大小
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 加粗
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font); // 单元格字体
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 单元格细边框
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平对齐方式
        cellStyle.setWrapText(true);// 单元格自动换行
        return cellStyle;
    }

    private static XSSFCellStyle setDetailStyle(XSSFWorkbook workbook, Class<?> colType, String option) {
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12); // 字体大小
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font); // 单元格字体
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 单元格细边框
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平对齐方式
        cellStyle.setWrapText(true);// 单元格自动换行

        //单元格格式
        if (colType == Date.class || colType == Timestamp.class) {
            XSSFDataFormat format = workbook.createDataFormat();
            if (option.equals("")) {
                cellStyle.setDataFormat(format.getFormat("yyyy/m/d"));
            } else if (option.equals("time")) {
                cellStyle.setDataFormat(format.getFormat("HH:MM:SS"));
            } else if (option.equals("datetime")) {
                cellStyle.setDataFormat(format.getFormat("yyyy/m/d HH:MM:SS"));
            }
        } else if (colType == String.class) {
            if (option.equals("string")) {
                XSSFDataFormat format = workbook.createDataFormat();
                cellStyle.setDataFormat(format.getFormat("@"));
            }
        }

        return cellStyle;
    }

    /**
     * 根据属性值，获得getter方法
     *
     * @param fieldname
     * @return
     */
    private static String toGetter(String fieldname) {
        if (fieldname == null || fieldname.length() == 0) {
            return null;
        }

        /* If the second char is upper, make 'get' + field name as getter name. For example, eBlog -> geteBlog */
        if (fieldname.length() > 2) {
            String second = fieldname.substring(1, 2);
            if (second.equals(second.toUpperCase())) {
                return new StringBuffer("get").append(fieldname).toString();
            }
        }

        /* Common situation */
        fieldname = new StringBuffer("get").append(fieldname.substring(0, 1).toUpperCase())
                .append(fieldname.substring(1)).toString();

        return fieldname;
    }

    /**
     * 设置数据有效性
     *
     * @param sheet
     * @param textList
     * @param firstRow
     * @param endRow
     * @param firstCol
     * @param endCol
     * @return
     */
    public static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {

        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        // DVConstraint constraint = new DVConstraint();
        constraint.setExplicitListValues(textList);

        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);

        // 数据有效性对象
        DataValidation data_validation = helper.createValidation(constraint, regions);
        //DataValidation data_validation = new DataValidation(regions, constraint);

        return data_validation;
    }

    /**
     * 循环向上转型, 获     * @param object : 子类对象
     * @param fieldName : 父类中     * @return 父类中     */

    public static Field getDeclaredField(Class<?> clazz, String fieldName){

        List<Field> fields = getAllFields(clazz);
        for (Field field : fields){
            if (fieldName.equals(field.getName())){
                return field;
            }
        }

        return null;
    }

    public static List<Field> getAllFields(Class<?> clazz){

        List<Field> fieldList = new ArrayList<>();
        while (clazz != null){
            fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
            clazz = clazz.getSuperclass();
        }
//        Field[] fields = new Field[fieldList.size()];
//        fieldList.toArray(fields);
        return fieldList;
    }


}